package com.hsnn.medstgmini.base.dict.dao.impl;


import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.DicExperttypeDao;
import com.hsnn.medstgmini.base.dict.model.DicExperttype;
import com.hsnn.medstgmini.dao.impl.HibernateSqlUtilImpl;
import com.hsnn.medstgmini.util.Pagination;

/**
 * @category 专家类型查询
 * @author 王炳
 * @date 2015年7月13日
 */
@Repository
public class DicExperttypeDaoImpl  extends BaseHibernateDAO implements DicExperttypeDao {
	

	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	
	@Autowired
	private HibernateSqlUtilImpl dao;
	
	/**
	 * @category 获取专家类型列表***
	 * @date 2015年7月13日
	 * @return page
	 */
	@Override
	public Pagination getDicExperttypeList(Pagination page){
		Map<String,String> params = new HashMap<String, String>();
		String expertTypeName = page.getConditions().get("expertTypeName");
		String status = page.getConditions().get("status");
		StringBuffer sql = new StringBuffer();
		sql.append("    select * from                                                                                              	 	")
		   .append("	(SELECT a.expert_type_name firstLevelName,b.expert_type_name secoLevelName,										")
		   .append("	c.expert_type_name thirdLevelName,d.expert_type_name forthLevelName,											")
		   .append("	(CASE WHEN d.dic_experttype_id IS NULL THEN 																	")
		   .append("	(CASE WHEN c.dic_experttype_id IS NULL THEN 																	")
		   .append("	(CASE WHEN b.dic_experttype_id IS NULL THEN a.dic_experttype_id													")
		   .append("	ELSE b.dic_experttype_id END)																					")
		   .append("	ELSE c.dic_experttype_id END)																					")
		   .append("	ELSE d.dic_experttype_id END) dicExperttypeId,																	")
		   .append("	(CASE WHEN d.dic_experttype_id IS NULL THEN 																	")
		   .append("	(CASE WHEN c.dic_experttype_id IS NULL THEN 																	")
		   .append("	(CASE WHEN b.dic_experttype_id IS NULL THEN a.expert_type_code													")
		   .append("	ELSE b.expert_type_code END)																					")
		   .append("	ELSE c.expert_type_code END)																					")
		   .append("	ELSE d.expert_type_code END) expertTypeCode,																	")
		   .append("	(CASE WHEN d.dic_experttype_id IS NULL THEN 																	")
		   .append("	(CASE WHEN c.dic_experttype_id IS NULL THEN 																	")
		   .append("	(CASE WHEN b.dic_experttype_id IS NULL THEN a.status															")
	       .append("	ELSE b.status END)																								")
		   .append("	ELSE c.status END)																								")
		   .append("	ELSE d.status END) status																						")
		   .append("	FROM  dic_experttype a																							")
		   .append("	LEFT JOIN dic_experttype b ON b.father_experttype_id=a.dic_experttype_id										")
		   .append("	LEFT JOIN dic_experttype c ON c.father_experttype_id=b.dic_experttype_id										")
		   .append("	LEFT JOIN dic_experttype d ON d.father_experttype_id=c.dic_experttype_id										")
		   .append("	WHERE a.expert_type_code LIKE '___' 																			")
		   .append("	AND (b.expert_type_code LIKE '_____' OR b.expert_type_code IS NULL)												")
		   .append("	AND (c.expert_type_code LIKE '_______' OR c.expert_type_code IS NULL) 											")
		   .append("	AND (d.expert_type_code LIKE '_________' OR d.expert_type_code IS NULL)											")
		   .append("	UNION																											")
		   .append("	SELECT f.expert_type_name firstLevelName,NULL secoLevelName,NULL thirdLevelName,								")
		   .append("	NULL forthLevelName,f.dic_experttype_id dicExperttypeId,f.expert_type_code expertTypeCode,f.status status 		")
		   .append("	FROM dic_experttype f WHERE f.expert_type_code LIKE '___'														")
		   .append("	UNION																											")
		   .append("	SELECT t.expert_type_name firstLevelName,p.expert_type_name secoLevelName,NULL thirdLevelName,					")
		   .append("	NULL forthLevelName,p.dic_experttype_id dicExperttypeId,p.expert_type_code expertTypeCode,p.status status		")
		   .append("	FROM  dic_experttype t 																							")
		   .append("	INNER JOIN dic_experttype p ON p.father_experttype_id=t.dic_experttype_id 										")
		   .append("	WHERE t.expert_type_code LIKE '___' AND p.expert_type_code LIKE '_____'											")
		   .append("	UNION																											")
		   .append("	SELECT q.expert_type_name firstLevelName,w.expert_type_name secoLevelName,e.expert_type_name thirdLevelName,	")
		   .append("	NULL forthLevelName,e.dic_experttype_id dicExperttypeId,e.expert_type_code expertTypeCode,e.status status		")
		   .append("	FROM  dic_experttype q 																							")
		   .append("	INNER JOIN dic_experttype w ON w.father_experttype_id=q.dic_experttype_id										")
		   .append("	INNER JOIN dic_experttype e ON e.father_experttype_id=w.dic_experttype_id										")
		   .append("	WHERE q.expert_type_code LIKE '___' 																			")
		   .append("	AND w.expert_type_code LIKE '_____'  																			")
		   .append("	AND e.expert_type_code LIKE '_______' 	)ps																		");
		if(StringUtils.isNotBlank(expertTypeName)){
			sql.append("    where ps.firstLevelName like :expertTypeName or ps.secoLevelName like :expertTypeName or ps.thirdLevelName like :expertTypeName ");
			params.put("expertTypeName", "%"+expertTypeName.trim()+"%");
		}
		if(StringUtils.isNotBlank(status)){
			sql.append("    where ps.status =:status ");
			params.put("status", status);
		}
		

		sql.append("	ORDER BY ps.firstLevelName,ps.secoLevelName,ps.thirdLevelName,ps.forthLevelName		");
		return dao.getSqlPageMapList(sql.toString(), params,page);
	}
	
	/**
	 * @category 停用启用
	 * @date 2015年7月13日
	 * @return page
	 */
	@SuppressWarnings("unchecked")
	@Override
	public int changeStatus(DicExperttype dicExperttype){
		Session session = sessionFactory.getCurrentSession();
		List<String> list = new ArrayList<String>();
		if("0".equals(dicExperttype.getStatus()+"")){
			StringBuffer sb = new StringBuffer()
			.append("select dic_experttype_id from dic_experttype where dic_experttype_id= :dicExperttypeId or (father_experttype_id=:dicExperttypeId and status <> 0 ) ");
			Query query = session.createSQLQuery(sb.toString());
			query.setString("dicExperttypeId", dicExperttype.getDicExperttypeId());
			List<Object> data = query.list();
			for(int i = 0;i<data.size();i++){
				list.add(data.get(i).toString());
			}
		}
		StringBuffer sql = new StringBuffer();
		sql.append("update dic_experttype  set ")
		  .append(" status= :status ,upd_datetime= SYSDATE() ,upd_user= :updUser  ")
		  .append(" where dic_experttype_id = :dicExperttypeId and status <> :status ");
		if("0".equals(dicExperttype.getStatus()+"")){
			sql.append(" or (expert_type_code like :expertTypeCode   )");
		}
		Query update = session.createSQLQuery(sql.toString());
		update.setString("status",dicExperttype.getStatus());
		update.setString("updUser", dicExperttype.getUpdUser());
		update.setString("dicExperttypeId", dicExperttype.getDicExperttypeId());
		if("0".equals(dicExperttype.getStatus()+"")){
			update.setString("expertTypeCode", dicExperttype.getExpertTypeCode()+"%");
		}
		int count = update.executeUpdate();
		session.flush();
		writeLogs(dicExperttype.getStatus()+"",dicExperttype.getDicExperttypeId(),list);
		return count;
	}
	
	/**
	 * @category  写日志
	 * @date 2015年7月13日
	 * @return page
	 */
	@SuppressWarnings("unchecked")
	@Override
	public void writeLogs(String status,String id,List<String> idList){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer detail = new StringBuffer();
		if(!"0".equals(status)){
			StringBuffer sb = new StringBuffer()
			.append("select  expert_type_name expertTypeName,status from dic_experttype where dic_experttype_id = :id ")
			.append(" union all ")
			.append(" select t.* from (select  expert_type_name expertTypeName,status from dic_experttype_log where  dic_experttype_id = :id order by upd_datetime desc limit 1) t");
			Query query1 = session.createSQLQuery(sb.toString());
			query1.setString("id", id);
			query1.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			List<Map<String,Object>> list = query1.list();
			if(list.size()==1){
				detail.append(";类型名称,")
				      .append(list.get(0).get("expertTypeName").toString())
				      .append(";状态,启用");
			}else{
				if(!list.get(0).get("expertTypeName").toString().equals(list.get(1).get("expertTypeName").toString())){
					detail.append(";类型名称,")
					      .append(list.get(0).get("expertTypeName").toString())
					      .append(",")
					      .append(list.get(1).get("expertTypeName").toString());
				}
				if(!list.get(0).get("status").toString().equals(list.get(1).get("status").toString())){
					detail.append(";状态,")
					      .append("1".equals(list.get(0).get("status").toString())?"启用":"停用")
					      .append(",")
					      .append("1".equals(list.get(1).get("status").toString())?"启用":"停用");
				}
				detail.delete(0, 1);
			}
			
		}
		StringBuffer sql = new StringBuffer();
		sql.append("insert into dic_experttype_log(log_id,dic_experttype_id,expert_type_code,expert_type_name,  ")
		   .append("father_experttype_id,status,remark,upd_datetime,upd_user,detail) select ")
		   .append(" UUID(),t.*,:detail from dic_experttype t where t.dic_experttype_id= :id ");
		if("0".equals(status)){
			sql.append(" or t.dic_experttype_id in (:idList)");
			//字段名,修改后数据,修改前数据
			detail.append("状态,停用,启用");
			
		}
		Query query=session.createSQLQuery(sql.toString());
		query.setString("id",id);
		if("0".equals(status)){
			query.setParameterList("idList", idList);
		}
		query.setString("detail", detail.toString());
		query.executeUpdate();
		session.flush();
	}

	/**
	 * 编辑***
	 * @param page
	 */
	@Override
	public void updateDicExperttype(Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		String dicExperttypeId = page.getConditions().get("dicExperttypeId");
		String expertTypeName = page.getConditions().get("expertTypeName");
		String updUser = page.getConditions().get("updUser");
		StringBuffer sql = new StringBuffer();
		sql.append(" update dic_experttype set expert_type_name=:expertTypeName, ")
		   .append(" upd_datetime=:updDatetime, upd_user=:updUser  ")
		   .append(" where dic_experttype_id=:dicExperttypeId  ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("dicExperttypeId",dicExperttypeId );
		query.setString("expertTypeName",expertTypeName );
		query.setString("updUser",updUser );
		query.setTimestamp("updDatetime", new Date());
		query.executeUpdate();
		session.flush();
		writeLogs("1",dicExperttypeId,null);
	}


	/**
	 * @category  根据id新增单条日志***
	 * @date 2015年7月13日
	 * @return page
	 */
	public void writeLog(String dicExperttypeId){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO dic_experttype_log SELECT UUID(),a.* FROM dic_experttype a where a.dic_experttype_id = :dicExperttypeId");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("dicExperttypeId", dicExperttypeId);
		query.executeUpdate();
		session.flush();
	}

	/**
	 * 新增
	 * @param page
	 */
	@Override
	public void addDicExperttype(Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		String dicExperttypeId = page.getConditions().get("dicExperttypeId");
		String expertTypeName = page.getConditions().get("expertTypeName");
		String updUser = page.getConditions().get("updUser");
		String fatherId = page.getConditions().get("fatherId");
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into dic_experttype  select f.* from (select :dicExperttypeId dicExperttypeId,  ")
		   .append(" case when t.dic_experttype_id is null then CONCAT((SELECT expert_type_code FROM dic_experttype WHERE dic_experttype_id = :fatherId),'01') else  ")//生成编码
		   .append(" CONCAT(LEFT(MAX(t.expert_type_code),(CHAR_LENGTH(MAX(t.expert_type_code))-CHAR_LENGTH((RIGHT(MAX(t.expert_type_code),CHAR_LENGTH(MAX(t.expert_type_code))-1)+1)))),(RIGHT(MAX(t.expert_type_code),CHAR_LENGTH(MAX(t.expert_type_code))-1)+1)) end dicExperttypeCode,  ")//生成编码
		   .append(" :expertTypeName expertTypeName,:fatherId fatherId,1 status,null remark,:updDatetime updDatetime,:updUser updUser from dic_experttype t WHERE t.father_experttype_id = :fatherId ) f  ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("dicExperttypeId",dicExperttypeId );
		query.setString("expertTypeName",expertTypeName );
		query.setString("updUser",updUser );
		query.setTimestamp("updDatetime", new Date());
		query.setString("fatherId", fatherId);
		query.executeUpdate();
		session.flush();
		writeLogs("1",dicExperttypeId,null);
	}



	/**
	 * 比对ID和name是否有重复***
	 * @param key
	 * @param value
	 * @param dicExperttypeId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<DicExperttype> getDicExperttypeBy(String key, String value,String dicExperttypeId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from dic_experttype where ")
		   .append(key)
		   .append(" = ? and  dic_experttype_id <> ? ");
		Query query = getSession().createSQLQuery(sql.toString());
		query.setParameter(0, value);
		query.setParameter(1,dicExperttypeId);
		return query.list();
	}
	
	/**
	 * 根据条件获取不分页数据
	 * @param page
	 * @return page
	 */
	@Override
	public Pagination getDicExperttypeNoPage(Pagination page){
		Map<String,String> params = new HashMap<String, String>();
		String expertTypeName = page.getConditions().get("expertTypeName");
		String status = page.getConditions().get("status");
		StringBuffer sql = new StringBuffer();
		sql.append("    select * from                (                                                                              	")
		   .append("	SELECT f.expert_type_name firstLevelName,NULL secoLevelName,NULL thirdLevelName,								")
		   .append("	case f.status when '0' then '停用' else '启用' end status 							                			")
		   .append("	FROM dic_experttype f WHERE f.expert_type_code LIKE '___'														")
		   .append("	UNION																											")
		   .append("	SELECT t.expert_type_name firstLevelName,p.expert_type_name secoLevelName,NULL thirdLevelName,					")
		   .append("	case p.status when '0' then '停用' else '启用' end status														")
		   .append("	FROM  dic_experttype t 																							")
		   .append("	INNER JOIN dic_experttype p ON p.father_experttype_id=t.dic_experttype_id 										")
		   .append("	WHERE t.expert_type_code LIKE '___' AND p.expert_type_code LIKE '_____'											")
		   .append("	UNION																											")
		   .append("	SELECT q.expert_type_name firstLevelName,w.expert_type_name secoLevelName,e.expert_type_name thirdLevelName,	")
		   .append("	case e.status when '0' then '停用' else '启用' end status														")
		   .append("	FROM  dic_experttype q 																							")
		   .append("	INNER JOIN dic_experttype w ON w.father_experttype_id=q.dic_experttype_id										")
		   .append("	INNER JOIN dic_experttype e ON e.father_experttype_id=w.dic_experttype_id										")
		   .append("	WHERE q.expert_type_code LIKE '___' 																			")
		   .append("	AND w.expert_type_code LIKE '_____'  																			")
		   .append("	AND e.expert_type_code LIKE '_______' 	)ps																		");
		if(StringUtils.isNotBlank(expertTypeName)){
			sql.append("    where ps.firstLevelName like :expertTypeName or ps.secoLevelName like :expertTypeName or ps.thirdLevelName like :expertTypeName ");
			params.put("expertTypeName", "%"+expertTypeName.trim()+"%");
		}
		if(StringUtils.isNotBlank(status)){
			sql.append("    where ps.status =:status ");
			params.put("status", "0".equals(status)?"停用":"启用");
		}
		

		sql.append("	ORDER BY ps.firstLevelName,ps.secoLevelName,ps.thirdLevelName		");
		page.setRows(dao.getSqlMapList(sql.toString(), params));
		return page;
	}
	
}	
